package com.cn.zengzeng.spbzengzeng.controller;

import com.alibaba.fastjson.JSONObject;
import com.cn.zengzeng.spbzengzeng.service.ImportService;
import com.cn.zengzeng.spbzengzeng.utils.VariableQiNiu;
import com.cn.zengzeng.spbzengzeng.vo.ActionResult;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.io.FileWriter;
import java.io.InputStream;
import java.util.List;

/**
 * @Description:
 * @Author: zhangzengzeng
 * @Date: 2021/10/26 19:34
 */
@RestController
@RequestMapping("/filer")
@Slf4j
public class FileController {

    @Resource
    private ImportService importService;
    @PostMapping(value = "/ExcelUpload")
    public String uploadExcel(HttpServletRequest request) throws Exception {
        log.info("1");
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        MultipartFile file = multipartRequest.getFile("filename");
        assert file != null;
        if (file.isEmpty()) {
            return "文件不能为空";
        }
        log.info("2");
        InputStream inputStream = file.getInputStream();
        List<List<Object>> list = importService.getBankListByExcel(inputStream, file.getOriginalFilename());

        inputStream.close();
        FileWriter fw = new FileWriter("/data/脚本.sql");
        FileWriter selefw = new FileWriter("/data/查询脚本.sql");
        FileWriter fancha = new FileWriter("/data/反查sql.sql");

        for (int i = 0; i < list.size(); i++) {
            List<Object> lo = list.get(i);
            //TODO 随意发挥
            String sql = "update  d_l_m_a_pointrecord60004_665 set KZZD3 = `desc` ," +
                    " `desc` = '"+ lo.get(4)+"' where  channel = '"+lo.get(0)+"' and " +
                    " recordType = '"+lo.get(1)+"'  and changeMode = '"+lo.get(2)+"'  and `desc` = '"+lo.get(3)+"'  and created>'2021-10-26 00:00:00' and KZZD3 is null;" ;
            String selSql = " select *  from d_l_m_a_pointrecord60004_665 " +
                    "where channel = '"+lo.get(0)+"' and  recordType = '"+lo.get(1)+"'  " +
                    "and changeMode = '"+lo.get(2)+"'  and `desc` = '"+lo.get(3)+"'  and created>'2021-10-26 00:00:00' and KZZD3 is null;";

            String xtrSql =" select *  from d_l_m_a_pointrecord60004_665 " +
                    "where channel = '"+lo.get(0)+"' and  recordType = '"+lo.get(1)+"'  " +
                    "and changeMode = '"+lo.get(2)+"'  and `desc` = '"+lo.get(4)+"' and KZZD3 = '"+lo.get(3)+"'  and created>'2021-10-26 00:00:00' and KZZD3 is null;";
            fw.write(sql+"\n");
            selefw.write(selSql+"\n");
            fancha.write(xtrSql+"\n");
            if(i%2==0){
                fw.write("select sleep(5) ;"+"\n");
            }
            fw.flush();
            selefw.flush();
            fancha.flush();
        }
        fw.close();
        selefw.close();
        fancha.close();
        return "上传成功";
    }
    @RequestMapping("/upload")
    public ActionResult uploadFile(@RequestParam("file") MultipartFile file){
        ActionResult result = new ActionResult();
        result.setCode("200");
        //上传文件
        String url = VariableQiNiu.uploadFile(file);
        result.setData(url);
        return result;
    }

    @PostMapping(value = "/uploadexcel")
    public String uploadexcel(HttpServletRequest request) throws Exception {
        log.info("1");
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        MultipartFile file = multipartRequest.getFile("filename");
        if (file.isEmpty()) {
            return "文件不能为空";
        }
        log.info("2");
        InputStream inputStream = file.getInputStream();
        List<List<Object>> list = importService.getBankListByExcel(inputStream, file.getOriginalFilename());

        inputStream.close();
        FileWriter fw = new FileWriter("/data/积分记录问题.sql");
        for (int i = 0; i < list.size(); i++) {
            List<Object> lo = list.get(i);
            Object members = lo.get(1);

            JSONObject jsonObject = JSONObject.parseObject(members.toString());
            JSONObject data = jsonObject.getJSONObject("data");
            String memberId = "'"+data.getString("memberId")+"'";
            String channelType = "'"+data.getString("channelType")+"'";
            String shopId = "'"+data.getString("shopId")+"'";
            String businessId = "'"+data.getString("businessId")+"'";
            String desc = "'"+data.getString("desc")+"'";
            Integer point = data.getInteger("point");
            String created = "'"+jsonObject.getString("occurrenceTs")+"'";
            String sql = "insert into error_event_pointrecord (memberId,channelType,shopId,businessId,`desc`,point,created) values  " +
                    "("+memberId+","+channelType+","+shopId+","+businessId+","+desc+","+point+","+created+");" ;
            fw.write(sql+"\n");
            fw.flush();
        }
        fw.close();
        return "处理完成";
    }

}
